from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError

from spiders.db.dbmysql import get_session, engine


def __init__(self):
    self.session = sessionmaker(bind=get_session().bind)


# 获取数据库表
def get_book_table():
    metadata = MetaData()
    return Table('book', metadata, autoload_with=engine)


# 添加新书
def add_book(book_data):
    session = get_session()
    try:
        book_table = get_book_table()
        insert = book_table.insert().values(book_data)
        session.execute(insert)
        session.commit()
        return True
    except SQLAlchemyError as e:
        session.rollback()
        print(e)
        return False
    finally:
        session.close()


# 查询书籍
def get_book(book_id):
    session = get_session()
    try:
        book_table = get_book_table()
        query = book_table.select().where(book_table.c.id == book_id)
        result = session.execute(query).fetchone()
        return result
    except SQLAlchemyError as e:
        return {"success": False, "message": str(e)}
    finally:
        session.close()


# 更新书籍
def update_book(book_id, update_data):
    session = get_session()
    try:
        book_table = get_book_table()
        update = book_table.update().where(book_table.c.id == book_id).values(update_data)
        result = session.execute(update)
        session.commit()
        if result.rowcount > 0:
            return True
        else:
            return False
    except SQLAlchemyError as e:
        session.rollback()
        return False
    finally:
        session.close()


# 删除书籍
def delete_book(book_id):
    session = get_session()
    try:
        book_table = get_book_table()
        delete = book_table.delete().where(book_table.c.id == book_id)
        result = session.execute(delete)
        session.commit()
        if result.rowcount > 0:
            return True
        else:
            return False
    except SQLAlchemyError as e:
        session.rollback()
        return {"success": False, "message": str(e)}
    finally:
        session.close()


# 示例：添加书籍
def example_usage():
    new_book = {
        'book_name': 'Example Book',
        'author_name': 'Example Author',
        'pic_url': 'http://example.com/image.jpg',
        'book_desc': 'This is an example book description',
        'visit_count': 12345,
        'update_time': '2024-09-08 12:00:00',
        'create_time': '2024-09-08 12:00:00'
    }
    result = add_book(new_book)
    print(result)


if __name__ == '__main__':
    print(get_book(1827696304309731328))
